Creating a record selection condition for one or more columns

You can create a record selection condition manually and/or by using the Expression Editor.

When entering a string, you can use the following special characters:

• %: Matches any string of zero or more characters. For example, Mc% searches for every name that begins with Mc or %bob% includes every name that contains bob.
• _:Matches a single character (as a wildcard). For example: ’Sm_th’ includes names that begin with Sm and end with th, such as Smith or Smyth. To search for an underscore character, use [_]".
• [..]: Includes a range or set of characters. For example, [CK]ars[eo] includes names Carsen, Karsen, Carson, and Karson or [M-Z]inger includes all words that end in inger with the first letter between M and Z, such as Ringer, Singer, or Zinger.

For more information, see documentation on how to use Transact-SQL.

For information on what SQLite operators can be used to create Record Selection Condition filters, see Using SQLite syntax with filtering.

To create a record selection condition:

1. From the Data Columns list, select a source column, header column or calculated column and then click the arrow to the left of the Record Selection Condition pane.

2. Use SQLite operators, such as < or = to create the condition. Use any amount of strings or columns as you need to create a condition.

   For example $EMPLOYEE_ID < 100 AND $SALARY > 100,000

   In this case only rows that satisfy both of these conditions are replicated in the replication task.

   The following example provides an example using SQL search pattern strings. Only rows that satisfy this condition are replicated.

   $EMPLOYEE_NAME IS ’Sm_th’

To create a record selection condition using the Expression Builder:

• Click Open Expression Builder. This button is located directly under the record selection condition box. Follow the directions for creating an expression in the section Using the Expression Builder.

Applying updates to specific columns only

You can define an expression that instructs Replicate only to apply UPDATEs when a user-defined condition has been met, for example, only when specific columns have changed. This is useful in situations when there are many updates in the source that the user has deemed not relevant for the target, as "irrelevant" updates will be ignored.

Limitations

• Does not support columns that do not have Before-Image data (e.g. LOB columns)
• Does not support the following sources (i.e. sources that do not support Before-Image records):
• ODBC with CDC
• Teradata
• Subject to the existing expression builder and filter limitations
• Content-based filtering may result in loss of data or data corruption. For example, if the Primary Key value changes (an UPDATE operation), the expression may ignore the UPDATE if the columns that were specified in the expression did not change. The result in this case would be that a "phantom" row with the old row contents will remain, even if a change was later applied to the columns specified in the expression.

How to

Assume that you have a source table named table1 with columns c1-c10 but you are only interested in updating columns c7, c8 and c9 on the target.

To do this, you would need to:

1. Open the Table Settings for table1 and select the Filter tab.

2. Click the Expression Builder button at the bottom right of the tab.

   The Expression Builder opens.

3. Optionally, select the Headers tab.

   Information note

   Although selecting the Headers tab is optional, selecting it will enable you to add $AR_H_OPERATION to your expression (as required in Step 4 below) simply by double-clicking the column on the left of the tab.

4. Enter the following expression in the Build Expression pane and then click OK:

   ($AR_H_OPERATION != 'UPDATE') OR

   (($AR_H_OPERATION = 'UPDATE') AND (( ifnull($BI__c7,0) != ifnull($c7,0)) OR ( ifnull($BI__c8,0) != ifnull($c8,0)) OR ( ifnull($BI__c9,0) != ifnull($c9,0))))

   The above expression means that changes will be applied to c7, c8 and c9 only if one of the following is true:

   • The operation is not an UPDATE.
   • The value of c7, c8 or c9 has changed as the result of an UPDATE operation.
   Information note

   When used in an expression, Before-Image columns must be prefixed with $BI__. For operations other than UPDATE, the value of the specified columns will be NULL.